
cd "/Users/chuangchen/Library/CloudStorage/OneDrive-UniversityofPittsburgh/Pela project"

use "Grand_Merge_2004-2023_LAPOP_AmericasBarometer_v1.0_w.dta", clear

recode vb10(1=1)(2=0)(else=.)

bysort pais year: gen total=_N
drop if vb10==.
bysort pais year: gen pid_total = _N
bysort pais year: egen pid_1 = sum(vb10)
gen pid_p = pid_1/pid_total


keep pais year pid_p pid_1 pid_total total

bysort pais year: gen temp = _n
keep if temp==1
drop temp

label define pais_es 1 "Mexico" 2 "Guatemala" 3 "El Salvador" 4 "Honduras" 5 "Nicaragua" 6 "Costa Rica" 7 "Panama" 8 "Colombia" 9 "Ecuador" 10 "Bolivia" 11 "Peru" 12 "Paraguay" 13 "Chile" 14 "Uruguay" 15 "Brazil" 17 "Argentina" 21 "Dominican Republic" 22 "Haiti" 23 "Jamaica" 25 "Trinidad y Tobago" 26 "Belice" 27 "Surinam" 28 "Bahamas" 30 "Grenada" 40 "Estados Unidos" 41 "Canada" .z "No se le preguntó en este año" 16 "Venezuela" 24 "Guyana", replace

drop if pais ==.z | pais==41 | pais==40

decode pais, gen(pais2)
drop pais
rename pais2 pais


egen median_pid_p=median(pid_p)
gen high_pid=(pid_p>median_pid_p)

save "LAPOP_pid.dta", replace


***********************************************


use "Grand_Merge_2004-2023_LAPOP_AmericasBarometer_v1.0_w.dta", clear

recode vic1ext(1=1)(2=0)(else=.)

bysort pais year: gen total=_N
drop if vic1ext==.
bysort pais year: gen vic1ext_total = _N
bysort pais year: egen vic1ext_1 = sum(vic1ext)
gen vic1ext_p = vic1ext_1/vic1ext_total


keep pais year vic1ext_p vic1ext_1 vic1ext_total total

bysort pais year: gen temp = _n
keep if temp==1
drop temp

label define pais_es 1 "Mexico" 2 "Guatemala" 3 "El Salvador" 4 "Honduras" 5 "Nicaragua" 6 "Costa Rica" 7 "Panama" 8 "Colombia" 9 "Ecuador" 10 "Bolivia" 11 "Peru" 12 "Paraguay" 13 "Chile" 14 "Uruguay" 15 "Brazil" 17 "Argentina" 21 "Dominican Republic" 22 "Haiti" 23 "Jamaica" 25 "Trinidad y Tobago" 26 "Belice" 27 "Surinam" 28 "Bahamas" 30 "Grenada" 40 "Estados Unidos" 41 "Canada" .z "No se le preguntó en este año" 16 "Venezuela" 24 "Guyana", replace

drop if pais ==.z | pais==41 | pais==40

decode pais, gen(pais2)
drop pais
rename pais2 pais


egen median_vic1ext_p=median(vic1ext_p)
gen high_crime=(vic1ext_p>median_vic1ext_p)

save "LAPOP_crime2010_2023.dta", replace

use "118346168barometro_de_las_americas_2004_v7_upload.dta", clear
recode vic1(1=1)(2=0)(else=.)

bysort pais year: gen total=_N
drop if vic1==.
bysort pais year: gen vic1_total = _N
bysort pais year: egen vic1_1 = sum(vic1)
gen vic1_p = vic1_1/vic1_total

keep pais year vic1_p vic1_1 vic1_total total
bysort pais year: gen temp = _n
keep if temp==1
drop temp

label define pais_es 1 "Mexico" 2 "Guatemala" 3 "El Salvador" 4 "Honduras" 5 "Nicaragua" 6 "Costa Rica" 7 "Panama" 8 "Colombia" 9 "Ecuador" 10 "Bolivia" 21 "Dominican Republic", replace

decode pais, gen(pais2)
drop pais
rename pais2 pais

egen median_vic1_p=median(vic1_p)
gen high_crime=(vic1_p>median_vic1_p)

save "LAPOP_crime_2004.dta", replace




use "113606129bar_metro_de_las_am_ricas_2006v6.dta", clear
recode vic1(1=1)(2=0)(else=.)

bysort pais year: gen total=_N
drop if vic1==.
bysort pais year: gen vic1_total = _N
bysort pais year: egen vic1_1 = sum(vic1)
gen vic1_p = vic1_1/vic1_total

keep pais year vic1_p vic1_1 vic1_total total
bysort pais year: gen temp = _n
keep if temp==1
drop temp

label define Pais 1 "Mexico" 2 "Guatemala" 3 "El Salvador" 4 "Honduras" 5 "Nicaragua" 6 "Costa Rica" 7 "Panama" 8 "Colombia" 9 "Ecuador" 10 "Bolivia" 11 "Peru" 12 "Paraguay" 13 "Chile" 14 "Uruguay" 15 "Brazil" 16 "Venezuela" 21 "Dominican Republic" 22 "Haiti" 23 "Jamaica" 24 "Guyana" 25 "Trinidad y Tobago" 41 "Estados Unidos" 40 "Canada" .z "No se le preguntó en este año", replace

drop if pais==41 | pais==40

decode pais, gen(pais2)
drop pais
rename pais2 pais

egen median_vic1_p=median(vic1_p)
gen high_crime=(vic1_p>median_vic1_p)

save "LAPOP_crime_2006.dta", replace






use "1488019932AmericasBarometer 2008 to upload v7.dta", clear
recode vic1(1=1)(2=0)(else=.)

bysort pais year: gen total=_N
drop if vic1==.
bysort pais year: gen vic1_total = _N
bysort pais year: egen vic1_1 = sum(vic1)
gen vic1_p = vic1_1/vic1_total

keep pais year vic1_p vic1_1 vic1_total total
bysort pais year: gen temp = _n
keep if temp==1
drop temp

label define Pais 1 "Mexico" 2 "Guatemala" 3 "El Salvador" 4 "Honduras" 5 "Nicaragua" 6 "Costa Rica" 7 "Panama" 8 "Colombia" 9 "Ecuador" 10 "Bolivia" 11 "Peru" 12 "Paraguay" 13 "Chile" 14 "Uruguay" 15 "Brazil" 16 "Venezuela" 21 "Dominican Republic" 22 "Haiti" 23 "Jamaica" 24 "Guyana" 25 "Trinidad y Tobago" 26 "Belize" 40 "Estados Unidos" 41 "Canada" .z "No se le preguntó en este año", replace

drop if pais==41 | pais==40

decode pais, gen(pais2)
drop pais
rename pais2 pais

egen median_vic1_p=median(vic1_p)
gen high_crime=(vic1_p>median_vic1_p)

save "LAPOP_crime_2008.dta", replace



use "LAPOP_crime_2004.dta", clear
merge 1:1 year pais high_crime using "LAPOP_crime_2006.dta", nogenerate 
merge 1:1 year pais high_crime using "LAPOP_crime_2008.dta", nogenerate
merge 1:1 year pais high_crime using "LAPOP_crime2010_2023.dta", nogenerate

keep year pais high_crime

save "LAPOP_crime.dta", replace






